using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Globalization;
using System.Web.Configuration;

/// <summary>
/// Shows unused resources in a grid, allowing the user to filter by language and delete
/// </summary>
public partial class UnusedResources : System.Web.UI.Page
{
    private Database db;
    /// <summary>
    /// Fills the raddio boxes for each language in the database
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack) {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT DISTINCT language FROM " + db.TablePrefix + "resources";
                Database.DataAdapter adapter = new Database.DataAdapter(cmd);
                DataTable dt = new DataTable();
                dt.Columns.Add("language");
                dt.Columns.Add("englishname");
                DbDataReader reader = cmd.ExecuteReader();
                string abbrev;
                while(reader.Read()) {
                    abbrev = reader.GetString(0);
                    if(abbrev.Length > 0) {
                        dt.Rows.Add(abbrev, Utils.GetSafeEnglishName(abbrev));
                    }
                }
                reader.Close();
                dt.DefaultView.Sort = "englishname";
                radLanguage.DataSource = dt;
                radLanguage.DataValueField = "language";
                radLanguage.DataTextField = "englishname";
                radLanguage.DataBind();
                if(radLanguage.Items.Count > 0) {
                    radLanguage.SelectedIndex = 0;
                }
                SelectLanguage(null, EventArgs.Empty);
            }
        }
        Page.ClientScript.RegisterClientScriptInclude("json", ResolveUrl("~/js/jquery.json-1.3.min.js"));
        Page.ClientScript.RegisterClientScriptInclude("resourcejs", ResolveUrl("~/js/resources.js"));
        ScriptManager.RegisterStartupScript(this, typeof(Page), "nojs", "$(function(){ $('#" + btnRefresh.ClientID + "').remove(); });", true);
    }

    /// <summary>
    /// Closes the database connection when rendering is done.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Unload(object sender, EventArgs e) {
        if(db != null && db.Connected) {
            db.CloseConnection();
        }
    }

    /// <summary>
    /// Filters unused resources according to language
    /// </summary>
    /// <param name="sender">The sender.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void SelectLanguage(object sender, EventArgs e) {
        if(radLanguage.SelectedItem != null) {
            InitializeDataSource();
            grdFindUnused.DataBind();
        }
    }

    /// <summary>
    /// Sets the select command for the dsResources data source
    /// </summary>
    private void InitializeDataSource() {
        StringBuilder sqlBuffer = new StringBuilder();
        string tablePrefix = db == null ? WebConfigurationManager.AppSettings["tableprefix"] : db.TablePrefix;
        switch(Constants.DatabaseInstanceType) {
            case Database.DatabaseType.MySQL:
                sqlBuffer.Append(@"SELECT t.resourceid, t.translation, (SELECT COUNT(r.language) FROM ").Append(tablePrefix).Append(@"resources AS r WHERE r.resourceid = t.resourceid) AS number FROM ")
                         .Append(tablePrefix).Append("resources AS t LEFT JOIN ").Append(tablePrefix).Append(@"elements AS e ON t.resourceid = e.resourceid 
                                   WHERE language = ?lang
                                   GROUP BY t.resourceid, t.translation
                                   HAVING COUNT(e.resourceid) = 0");
                break;
            case Database.DatabaseType.SQLServer:
                sqlBuffer.Append(@"SELECT t.resourceid, t.translation, (SELECT COUNT(r.language) FROM ").Append(tablePrefix).Append("resources AS r WHERE r.resourceid = t.resourceid) AS number FROM ")
                         .Append(tablePrefix).Append("resources AS t LEFT JOIN ").Append(tablePrefix).Append(@"elements AS e ON t.resourceid = e.resourceid 
                                   WHERE language = @lang
                                   GROUP BY t.resourceid, t.translation
                                   HAVING COUNT(e.resourceid) = 0");
                break;
        }
        dsResources.SelectCommand = sqlBuffer.ToString();
        if(dsResources.SelectParameters.Count == 0) {
            dsResources.SelectParameters.Add("lang", TypeCode.String, radLanguage.SelectedValue);
        } else {
            dsResources.SelectParameters["lang"].DefaultValue = radLanguage.SelectedValue;
        }
    }

    /// <summary>
    /// Shows the number of items on all pages of the GridView
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.SqlDataSourceStatusEventArgs"/> instance containing the event data.</param>
    protected void dsResources_Selected(object sender, SqlDataSourceStatusEventArgs e) {
        lblNumUnused.Text = string.Format("{0} Unused Resources for {1}", e.AffectedRows, radLanguage.SelectedItem.Text);
    }

    /// <summary>
    /// Prompts the user to confirm deletion of a resource when the 'Delete' button is clicked
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewCommandEventArgs"/> instance containing the event data.</param>
    protected void grdFindUnused_RowCommand(object sender, GridViewCommandEventArgs e) {
        InitializeDataSource();
        grdFindUnused.DataBind();
        switch(e.CommandName) {
            case "deleteresource":
                PromptDelete(e.CommandArgument.ToString());
                break;
        }
    }

    /// <summary>
    /// Changes the page shown in the GridView
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewPageEventArgs"/> instance containing the event data.</param>
    protected void grdFindUnused_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        grdFindUnused.PageIndex = e.NewPageIndex;
        InitializeDataSource();
        grdFindUnused.DataBind();
    }

    /// <summary>
    /// Handles the Sorting event of the resource grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewSortEventArgs"/> instance containing the event data.</param>
    protected void grdFindUnused_Sorting(object sender, GridViewSortEventArgs e) {
        string oldExpression = grdFindUnused.SortExpression,
               newExpression = e.SortExpression;
        if(oldExpression.IndexOf(newExpression) < 0) {
            if(oldExpression.Length > 0) {
                e.SortExpression = newExpression + "," + oldExpression;
            } else {
                e.SortExpression = newExpression;
            }
        } else {
            e.SortExpression = oldExpression;
        }
        InitializeDataSource();
        grdFindUnused.DataBind();
    }


    #region Deleting
    /// <summary>
    /// Prompts the user to confirm deletion of multiple selected resources
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnDeleteSelected_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 1;
        List<string> ids = new List<string>();
        foreach(GridViewRow row in grdFindUnused.Rows) {
            if((row.Cells[2].FindControl("chkSelect") as CheckBox).Checked) {
                ids.Add((row.Cells[2].FindControl("btnDelete") as ImageButton).CommandArgument);
            }
        }
        if(ids.Count == 0) {
            litConfirmDelete.Text = "<p>No resources were selected</p>";
            btnOk.Visible = false;
        } else {
            string[] idAry = ids.ToArray();
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                string inClause = db.GenerateInClause("resourceid", idAry, false);
                Session["ids"] = inClause;
                cmd.CommandText = "SELECT COUNT(language) FROM " + db.TablePrefix + "resources WHERE " + inClause;
                try {
                    string number = cmd.ExecuteScalar().ToString();
                    litConfirmDelete.Text = string.Format("<p>Are you sure you want to delete {0} resources.</p><p>Across all languages, {1} translations will be deleted.</p>", idAry.Length, number);
                } catch {
                    litConfirmDelete.Text = "<p>Error loading resource</p>";
                }
            }
            btnOk.Text = "Delete Resources";
            btnOk.Visible = true;
        }
    }

    /// <summary>
    /// Confirms deletion of a resource and removes it from the database.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnOk_Click(object sender, EventArgs e) {
        db = new Database();
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            if(ViewState["transid"] != null) {
                cmd.CommandText = "DELETE FROM " + db.TablePrefix + "resources WHERE resourceid = @p";
                cmd.AddParameterWithValue("@p", ViewState["transid"].ToString());
                cmd.ExecuteNonQuery();
                ViewState["transid"] = null;
            } else if(Session["ids"] != null) {
                cmd.CommandText = "DELETE FROM " + db.TablePrefix + "resources WHERE " + Session["ids"].ToString();
                cmd.ExecuteNonQuery();
            }
        }
        mltMain.ActiveViewIndex = 0;
        InitializeDataSource();
        grdFindUnused.DataBind();
    }

    /// <summary>
    /// Prompts to delete a single resource.
    /// </summary>
    /// <param name="resourceId">The resource ID.</param>
    private void PromptDelete(string resourceId) {
        mltMain.ActiveViewIndex = 1;
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT COUNT(language) FROM " + db.TablePrefix + "resources WHERE resourceid = @tID";
            cmd.AddParameterWithValue("@tID", resourceId);
            ViewState["transid"] = resourceId;
            try {
                string number = cmd.ExecuteScalar().ToString();
                cmd.CommandText = "SELECT translation FROM " + db.TablePrefix + "resources WHERE resourceid = @tID AND language = @lang";
                cmd.AddParameterWithValue("@lang", radLanguage.SelectedValue);
                string resource = cmd.ExecuteScalar().ToString();
                litConfirmDelete.Text = string.Format("<p>Are you sure you want to delete resource #{0} - '{1}'.</p><p>This resource is available in {2} languages, which will also be deleted.</p>", resourceId, resource, number);
            } catch {
                litConfirmDelete.Text = "<p>Error loading resource</p>";
            }
            btnOk.Text = "Delete Resource";
            btnOk.Visible = true;
        }
    }

    /// <summary>
    /// Redirects the user to the 'Unused resources' page.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnCancelDelete_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 0;
        InitializeDataSource();
        grdFindUnused.DataBind();
    }
    #endregion
}
